package net.common.db;

import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;

import net.common.service.PageBean;
import net.common.util.RegularUtils;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

/**
 * dbutils扩展
 * 
 * @author 孙树林
 * 
 */
public class GeneralQueryRunner extends QueryRunner {

	/**
	 * 构造器
	 * 
	 */
	public GeneralQueryRunner() {
		super();
	}

	/**
	 * 构造器
	 * 
	 * @param ds
	 */
	public GeneralQueryRunner(DataSource ds) {
		super(ds);
	}

	/**
	 * 执行SQL INSERT语句，需要生成主键
	 * 
	 * @param conn
	 * @param sql
	 * @param bean
	 * @return
	 * @throws SQLException
	 */
	public int insertKey(String sql, Object bean) throws SQLException {
		return this.insert(sql, bean, true);
	}

	/**
	 * 执行SQL INSERT语句，不需要生成主键
	 * 
	 * @param conn
	 * @param sql
	 * @param bean
	 * @return
	 * @throws SQLException
	 */
	public int insert(String sql, Object bean) throws SQLException {
		return this.insert(sql, bean, false);
	}

	/**
	 * 执行SQL INSERT语句
	 * 
	 * @param conn
	 *            数据连接
	 * @param sql
	 *            insert语句
	 * @param bean
	 *            参数BEAN
	 * @param retKey
	 *            是否返回插入记录后生成的主键
	 * @return 保存数据主键
	 * @throws SQLException
	 */
	private int insert(String sql, Object bean, boolean retKey)
			throws SQLException {
		Connection conn = ConnectionManager.getConnection();
		int key = 0;
		if (conn == null) {
			throw new SQLException("Null connection");
		}
		if (sql == null) {
			throw new SQLException("Null SQL statement");
		}
		PreparedStatement stmt = null;
		try {
			if (!retKey) {
				stmt = this.prepareStatement(conn, sql);
			} else {
				stmt = conn.prepareStatement(sql,
						Statement.RETURN_GENERATED_KEYS);
			}
			this.fillStatementWithBean(stmt, bean, insertBeanProperties(sql)); // 解析insert语句，获取bean使用属性
			key = stmt.executeUpdate();
			if (retKey) {
				ResultSet rs = stmt.getGeneratedKeys();
				if (rs.next()) {
					key = rs.getInt(1);
				}
				rs.close();
			}
		} finally {
			close(stmt);
		}
		return key;
	}

	/**
	 * 执行SQL UPDATE语句
	 * 
	 * @param conn
	 * @param sql
	 * @param bean
	 * @return
	 * @throws SQLException
	 */
	public int updateByBean(String sql, Object bean) throws SQLException {
		Connection conn = ConnectionManager.getConnection();
		int key = 0;
		if (conn == null) {
			throw new SQLException("Null connection");
		}
		if (sql == null) {
			throw new SQLException("Null SQL statement");
		}
		PreparedStatement stmt = null;
		try {
			stmt = this.prepareStatement(conn, sql);
			this.fillStatementWithBean(stmt, bean, updateBeanProperties(sql)); // 解析update语句，获取bean使用属性
			key = stmt.executeUpdate();
		} finally {
			close(stmt);
		}
		return key;
	}

	/**
	 * 执行SQL DELETE
	 * 
	 * @param conn
	 * @param sql
	 * @param bean
	 * @return
	 * @throws SQLException
	 */
	public int delete(String sql, Object bean) throws SQLException {
		Connection conn = ConnectionManager.getConnection();
		int key = 0;
		if (conn == null) {
			throw new SQLException("Null connection");
		}
		if (sql == null) {
			throw new SQLException("Null SQL statement");
		}
		PreparedStatement stmt = null;
		try {
			stmt = this.prepareStatement(conn, sql);
			this.fillStatementWithBean(stmt, bean, whereBeanProperties(sql)); // 解析delete语句，获取bean使用属性
			key = stmt.executeUpdate();
		} finally {
			close(stmt);
		}
		return key;
	}

	/**
	 * 删除单独的一条记录。
	 * 
	 * @param sql
	 * @param id
	 * @return
	 * @throws SQLException
	 */
	public int deleteOne(String sql, Object[] id) throws SQLException {
		
		Connection conn = ConnectionManager.getConnection();
		return update(conn,sql, id);
	}

	/**
	 * oracle分页查询
	 * 
	 * @param <T>
	 * @param conn
	 * @param sql
	 * @param rsh
	 * @param bean
	 * @param pageBean
	 * @param key
	 * @return
	 * @throws SQLException
	 */
	public <T> T oracleQueryByBean(String sql, ResultSetHandler<T> rsh,
			Object bean, PageBean pageBean, String key) throws SQLException {
		// 统计记录数
		this.totalRec(sql, bean, pageBean, key);
		sql = "select * from (select *, rownum rn from (" + sql
				+ ") GENERALQUERY where rownum<=?) where rn>=?";
		int endRow = pageBean.getEndRow();
		int startRow = pageBean.getStartRow();
		return this.queryByBean(sql, rsh, pageBean, new Object[] { endRow,
				startRow });
	}

	/**
	 * mysql分页查询
	 * 
	 * @param <T>
	 * @param conn
	 * @param sql
	 * @param rsh
	 * @param bean
	 * @param pageBean
	 * @param key
	 * @return
	 * @throws SQLException
	 */
	public <T> T mysqlQueryByBean(String sql, ResultSetHandler<T> rsh,
			Object bean, PageBean pageBean, String key) throws SQLException {
		// 统计记录数
		this.totalRec(sql, bean, pageBean, key);
		sql = sql + " limit ?,?";
		int endRow = pageBean.getCurrentRec();
		int startRow = pageBean.getStartRow();
		return this.queryByBean(sql, rsh, bean, new Object[] { startRow,
				endRow });
	}

	/**
	 * 统计记录数
	 * 
	 * @param conn
	 * @param sql
	 * @param bean
	 * @param pageBean
	 * @throws SQLException
	 */
	private void totalRec(String sql, Object bean, PageBean pageBean, String key)
			throws SQLException {
		sql = "select count(" + (key != null ? key : "*") + ") c from (" + sql
				+ ") GENERALQUERY";
		Long o = queryByBean(sql, new ScalarHandler<Long>(), bean);
		pageBean.setTotalRec(o.intValue());
	}

	/**
	 * 分页查询
	 * 
	 * @param <T>
	 * @param conn
	 * @param sql
	 * @param rsh
	 * @param bean
	 * @param pageBean
	 * @param key
	 * @return
	 */
	public <T> T querySplitPage(String sql, ResultSetHandler<T> rsh,
			Object bean, PageBean pageBean, String key) throws SQLException {
		Connection conn = ConnectionManager.getConnection();
		DatabaseMetaData dmd = conn.getMetaData();
		String productName = dmd.getDatabaseProductName();
		if (productName.equalsIgnoreCase("mysql")) {
			return this.mysqlQueryByBean(sql, rsh, bean, pageBean, key);
		} else if (productName.equalsIgnoreCase("oracle")) {
			return this.oracleQueryByBean(sql, rsh, bean, pageBean, key);
		} else {
			throw new SQLException("");
		}
	}

	/**
	 * 分页查询
	 * 
	 * @param <T>
	 * @param conn
	 * @param sql
	 * @param rsh
	 * @param bean
	 * @param pageBean
	 * @param key
	 * @return
	 */
	public <T> T querySplitPage(String sql, ResultSetHandler<T> rsh,
			PageBean pageBean, String key) throws SQLException {
		Connection conn = ConnectionManager.getConnection();
		DatabaseMetaData dmd = conn.getMetaData();
		String productName = dmd.getDatabaseProductName();
		if (productName.equalsIgnoreCase("mysql")) {
			return this.mysqlQueryByBean(sql, rsh, null, pageBean, key);
		} else if (productName.equalsIgnoreCase("oracle")) {
			return this.oracleQueryByBean(sql, rsh, null, pageBean, key);
		} else {
			throw new SQLException("");
		}
	}

	/**
	 * 执行SQL SELECT
	 * 
	 * @param <T>
	 * @param conn
	 * @param sql
	 * @param rsh
	 * @return
	 * @throws SQLException
	 */
	public <T> T queryByBean(String sql, ResultSetHandler<T> rsh)
			throws SQLException {
		return this.queryByBean(sql, rsh, null);
	}

	/**
	 * 执行SQL SELECT
	 * 
	 * @param <T>
	 * @param conn
	 * @param sql
	 * @param rsh
	 * @param bean
	 * @return
	 * @throws SQLException
	 */
	public <T> T queryByBean(String sql, ResultSetHandler<T> rsh, Object bean,
			Object... params) throws SQLException {
		Connection conn = ConnectionManager.getConnection();
		if (conn == null) {
			throw new SQLException("Null connection");
		}
		if (sql == null) {
			throw new SQLException("Null SQL statement");
		}
		if (rsh == null) {
			throw new SQLException("Null ResultSetHandler");
		}
		PreparedStatement stmt = null;
		ResultSet rs = null;
		T result = null;
		try {
			stmt = this.prepareStatement(conn, sql);
			String[] where = whereBeanProperties(sql);

			this.fillStatementWithBean(stmt, bean, params, where);

			rs = this.wrap(stmt.executeQuery());
			result = rsh.handle(rs);
		} finally {
			try {
				close(rs);
			} finally {
				close(stmt);
			}
		}
		return result;
	}
	
	/**
	 * 获取单条记录的操作方法
	 * 
	 * @param sql
	 * @param rsh
	 * @param params
	 * @return
	 * @throws SQLException
	 */
	public <T> T queryOne(String sql, ResultSetHandler<T> rsh, Object... params) throws SQLException {
		Connection conn = ConnectionManager.getConnection();
		return this.query(conn, sql, rsh,params);
	}

	/**
	 * 解析insert语句
	 * 
	 * @param sql
	 * @return
	 */
	private String[] insertBeanProperties(String sql) {
		String properties = RegularUtils.search(
				"\\(([\\w]+|([\\w]+,)+\\w+)\\)", sql);
		String[] pro = properties.substring(1, properties.length() - 1).split(
				",");
		String sybol = RegularUtils.search("\\(([\\?]|([\\?],)+\\?)\\)", sql);
		String[] sy = sybol.substring(1, sybol.length() - 1).split(",");
		List<String> s = new ArrayList<String>();
		for (int i = 0; i < sy.length; i++) {
			if (sy[i].equals("?")) {
				s.add(pro[i]);
			}
		}
		String[] rs = new String[s.size()];
		s.toArray(rs);
		return rs;
	}

	/**
	 * 解析update语句，
	 * 
	 * @param sql
	 * @return
	 */
	private String[] updateBeanProperties(String sql) {
		String reg = "\\w+=\\?";
		List<String> properties = RegularUtils.searchList(reg, sql);
		String[] s = new String[properties.size()];
		for (int i = 0; i < properties.size(); i++) {
			s[i] = properties.get(i).replaceAll("=\\?", "");
		}
		return s;
	}

	/**
	 * 解析where
	 * 
	 * @param sql
	 * @return
	 */
	private String[] whereBeanProperties(String sql) {
		return updateBeanProperties(sql);
	}

	public void fillStatementWithBean(PreparedStatement stmt, Object bean,
			PropertyDescriptor[] properties, Object[] param)
			throws SQLException {
		int r_len = 0;
		int p_len = 0;
		if (param != null) {
			r_len = param.length;
		}
		Object[] params = null;
		if(bean != null) {
			p_len = properties.length;
			params = new Object[p_len + r_len];
			for (int i = 0; i < properties.length; i++) {
				PropertyDescriptor property = properties[i];
				Object value = null;
				Method method = property.getReadMethod();
				if (method == null) {
					throw new RuntimeException("No read method for bean property "
							+ bean.getClass() + " " + property.getName());
				}
				try {
					value = method.invoke(bean, new Object[0]);
				} catch (InvocationTargetException e) {
					throw new RuntimeException("Couldn't invoke method: " + method,
							e);
				} catch (IllegalArgumentException e) {
					throw new RuntimeException(
							"Couldn't invoke method with 0 arguments: " + method, e);
				} catch (IllegalAccessException e) {
					throw new RuntimeException("Couldn't invoke method: " + method,
							e);
				}
				params[i] = value;
			}
		} else {
			params = new Object[p_len + r_len];
		}
		for (int i = 0; i < r_len; i++) {
			params[p_len++] = param[i];
		}
		fillStatement(stmt, params);
	}

	public void fillStatementWithBean(PreparedStatement stmt, Object bean,
			Object[] param, String... propertyNames) throws SQLException {
		PropertyDescriptor[] sorted = null;
		if(bean != null) {
			PropertyDescriptor[] descriptors;
			try {
				descriptors = Introspector.getBeanInfo(bean.getClass())
				.getPropertyDescriptors();
			} catch (IntrospectionException e) {
				throw new RuntimeException("Couldn't introspect bean "
						+ bean.getClass().toString(), e);
			}
			sorted = new PropertyDescriptor[propertyNames.length];
			for (int i = 0; i < propertyNames.length; i++) {
				String propertyName = propertyNames[i];
				if (propertyName == null) {
					throw new NullPointerException("propertyName can't be null: "
							+ i);
				}
				boolean found = false;
				for (int j = 0; j < descriptors.length; j++) {
					PropertyDescriptor descriptor = descriptors[j];
					if (propertyName.equals(descriptor.getName())) {
						sorted[i] = descriptor;
						found = true;
						break;
					}
				}
				if (!found) {
					throw new RuntimeException("Couldn't find bean property: "
							+ bean.getClass() + " " + propertyName);
				}
			}
		}
		fillStatementWithBean(stmt, bean, sorted, param);
	}

}
